MS SQL Server - Notas de instalação e configuração Server
Acessando o servidor remotamente
1-Anote dados sobre o servidor para futuros acessos:
Versão do Windows : 8.1
Versão do SQL Server : 2016
Servidor : \\SRVXXX
instância do ms sql server : SRVXXX\INSTANCIAXXX
senha sa : XXX
Autenticação : Local : windows authentication - username : SRVXXX\USUXXX
2-Sugiro fazer uma conexão remota com o servidor via ‘Conexão de Área de Trabalho Remota’
Isso agiliza muito o processo de configuração
Clique no ‘Iniciar’ do Windows e digite ‘conex’ e vai ser exibido ‘conexão de área de trabalho remota’
No Nome do Servidor digite ‘SRVXXX’
No Nome do usuário digitar ‘SRVXXX\USUXXX’
Na senha do usuário digitar ‘SenhaXXX’
3-Permitindo acesso remoto ao servidor: Windows 8
Clique com o botão direito do mouse no ícone ‘Iniciar’
Selecione ‘Sistema’
Na lateral esquerda clique em ‘Configurações Remotas’
Selecione a caixa ‘Permitir conexões remotas com este computador’
4-Criando o novo usuário para acesso remoto ao sql server:
Não deve ser o sa por razões de segurança.
Sempre aconselho a criar um com permissão somente de leitura e outro com permissão de escrita. Já o acesso ao admin do sistema é coisa para usuários avançados.
-Abra o MS SQL Server Management Studio
-Abra a aba ‘Segurança’
-Clique na aba ‘Logons’ com o botão direito do mouse e selecione ‘Novo Logon’
-Digite no box ‘Nome de Logon’ o nome do novo usuário : usucomum
-Selecione o box ‘Autenticação do SQL Server’
-Digite no box ‘Senha’ a senha do usuário SenhaXXX
-Digite no box ‘Confirmar Senha’ a senha do usuário novamente
-Deixe o box ‘Impor Politica de Senha’ ativado
-Deixe o box ‘Impor vencimento de Senha’ desativado
-Deixe o box ‘O usuário deve trocar a senha no próximo login’ desativado
-Na aba da esquerda em ‘Funções de servidor’ (Server Roles) selecionar as funções de servidor que o usuário deverá ter acesso.
O default é só public. Deixei só public.
- Na aba da esquerda em ‘Mapeamento de usuário’ (User Mapping) selecionar os bancos de dados que o usuário deverá ter acesso.
O default é nenhum. Adicionei Agenda e Adventureworks2014.
-Na parte inferior verifique as permissões administrativas dele. No meu ficou só public.
-Clique no botão ‘OK’ e o usuário será criado.
5- Modos de autenticação aceitos pelo SQL Server.
Por default apenas o login pelo Windows vem selecionado.
Para selecionar o login pelo SQL Server faça o seguinte:
- Abra o MS SQL Server Management Studio
-Clique com o Botão direito do mouse em cima do servidor
-Selecionar ‘Propriedades’
-Abra a aba ‘Segurança’
-Na área ‘autenticação do servidor‘ selecione o box ‘Modo de autenticação do SQL Server e do Windows’.
6-Acessando o MS SQL Server Configuration Manager
-Quando você instala o SQL Server é também instalado o Configuration Manager mas não há atalhos para ele.
-Procure por 'Configuration' ou vá até pasta C:\Windows\SysWOW64 encontramos o arquivo SQLServerManager14.msc
-Abra o MS SQL Server Configuration Manager ( ou o arquivo SQLServerManager14.msc)
-Vá em todos os menus e habilite o protocolo TCPIP e Named Pipes em ‘Configuração de rede do SQL Server’
‘Protocolos para o SQL Express’
- Verifique também a porta que eles estão funcionando.
-Selecione a aba 'Protocols for INSTANCIAXXX' - TCP/IP e clique com o botão direito do mouse
-Selecione 'Propriedades'
-Selecione a aba 'IP Adresses'
-Em todas as linhas onde tenha o 'TCP DYNAMIC PORTS' igual a zero apague o zero.
'TCP DYNAMIC PORTS' obriga a porta de conexão a ser dinâmica e isso dificulta o acesso remoto.
-Abra a aba 'Protocols for INSTANCIAXXX', Clique com o botão direito do mouse em 'TC/PIP' e selecione propriedades
-Selecione a aba 'IP Adresses' e vá até o item 'IPAll'
-Na linha 'TCP Port' coloque o número 1433 (porta default do ms sql srv)
-Clique na aba 'SQL Server Services' e verifique se o serviço 'SQL Server Browser' está sendo executado.
Se não estiver vá no Painel de Controle, Ferramentas Administrativas e inicie o Serviço
7-Importante
-Na conexão com o servidor devemos indicar o servidor e a instância do SQL Server para conectar a ele.
-Portanto ao tentar conectar somente com o servidor será exibido um erro de conexão.
No meu caso a conexão correta ficou sendo : SRVXXX\INSTANCIAXXX
8-Faça a conexão local com o SQL SRV Management Studio e verifique que o novo usuário funcione normalmente.
Server type : Database Engine
Server name : SRVXXX\INSTANCIAXXX
Authentication: SQL Server Authentication
Login : usuXXX
Senha : senhaXXXX
Só prossiga o passo 9 se o 8 foi executado com sucesso.
9-Antes de abrir a porta no firewall verifique se é realmente ele que está bloqueando seu acesso.
No micro ‘servidor’ desabilite o firewall nas conexões locais e faça o teste de conexão com o SQL Server Management Studio.
Se o problema continuar ainda tem mais coisas e deixe o firewall por último.
NÃO SE ESQUEÇA DE REHABILITAR OS FIREWALLS DOS DO SERVER E DO CLIENT. Ao fazer isso você perderá a conexão remota.
10-Descobrindo onde o programa sql server esta localizado.
-Para abri o firewall precisamos localizar corretamente o programa sql server que esta sendo executado.
-Para isto abra o MS SQL Server Management Studio
-Clique com o botão direito do mouse no Servidor e em seguida em 'Propriedades'
-Na caixa 'Root Directory' esta o programa :
C:\Program Files\Microsoft SQL Server\MSSQL12.INSTANCIAXXX\MSSQL
11-Abrindo o firewall para o MS SQL SRV no servidor:
-Nota : O Firewall no cliente não atrapalha a conexão com o servidor.
Por padrão, as portas normais usadas pelo SQL Server e os serviços de mecanismo de banco de dados associados são:
TCP
1433 Database Engine
1434 SQL Admin Connection
4022 SQL Service Broker
135 SQL Debugger/RPC
Portanto abriremos as portas 1433, 1434, 4022, 135
UDP
1434 SQL Browser
-Vamos abrir o TCPIP servidor para a rede local nas portas 1433, 1434, 4022, 135:
-Vá no Windows Firewall do servidor
-Clique em ‘Configurações Avançadas’
-Clique em ‘Regras de Entrada’
-No painel direito, em Ações, clique em 'Nova regra'.
-Em Tipo de regra, clique em 'Portas'. Selecione Avançar.
-Em Portas e Procotocolos selecione 'TCPIP' e em Specific local ports digite 1433, 1434, 4022, 135. Clique em Next
-Selecione 'Habilitar conexão'
-Na janela profile deixe marcado apenas o perfil 'Private' (Não habilite Domain e Public)
-Em Nome, digite um nome para a regra. Selecione Concluir.
-Vamos abrir o UDP servidor para a rede local na porta 1434:
-Vá no Windows Firewall do servidor
-Clique em ‘Configurações Avançadas’
-Clique em ‘Regras de Entrada’
-No painel direito, em Ações, clique em 'Nova regra'.
-Em Tipo de regra, clique em 'Portas'. Selecione Avançar.
-Em Portas e Procotocolos selecione 'UDP' e em Specific local ports digite 1434. Clique em Next
-Selecione 'Habilitar conexão'
-Na janela profile deixe marcado apenas o perfil 'Private' (Não habilite Domain e Public)
-Em Nome, digite um nome para a regra. Selecione Concluir.
Nota : sem a abertura deste ultimo port não é possível fazer conexão Remota.
Extras
1-Dando acesso a funções administrativas de servidor ao usuário:
Todos os usuários do SQL server podem executar algumas tarefas administrativas do Windows NT.
Na instalação default, nenhum usuário tem privilégios administrativos na máquina local.
Caso precise, altere:
- Abra o MS SQL Server Management Studio
-Clique com o Botão direito do mouse em cima do servidor
-Selecionar ‘Propriedades’
-Clique na Aba ‘Permissões’
-Selecione o ‘Logon’ do usuário
-Selecione o privilégio administrativo que deseja para ele.
2-Integrando o Excel e o SQL Server
-No SQL Server 2016 já vem instalado o ‘Microsoft.ACE.OLEDB.16.0’
-É necessário instalar no servidor o ‘Microsoft.ACE.OLEDB.12.0’ através do ‘Data Connectivity Components’
3-Para identifica a instância do SQL Server que esta sendo executada no momento.
O programa é chamado sqlservr.exe. Ele normalmente encontra-se em:
C:\Program Files\Microsoft SQL Server\MSSQL13.<InstanceName>\MSSQL\Binn\sqlservr.exe
C:\Program Files\Microsoft SQL Server\MSSQL12.INSTANCIAXXX\MSSQL\Binn\sqlservr.exe